package com.example.service;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.model.Person;

public class DbUseService {
	private DbOpenHelper dbOpenHelper;
	
	public DbUseService(Context context){
		dbOpenHelper=new DbOpenHelper(context,"asisoft",null,1);
	}

	public void save(Person person){
		SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
		db.execSQL("insert into person(id,name,age) values(?,?,?)", new Object[]{person.getId(),person.getName(),person.getAge()});
		//db.close();
	}
	
	public void update(Person person){
		SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
		db.execSQL("update person set name=?,age=? where id=?", new Object[]{person.getName(),person.getAge(),person.getId()});
		//db.close();
	}
	
	public void delete(Long id){
		SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
		db.execSQL("delete from person where id=?", new Object[]{id});
		//db.close();
	}
	
	public Person getPerson(Long id){
		Person person=null;
		SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from person where id=?", new String[]{id.toString()});
		if(cursor.moveToFirst()){
			person = new Person();
			person.setId(cursor.getLong(cursor.getColumnIndex("id")));
			person.setName(cursor.getString(cursor.getColumnIndex("name")));
			person.setAge(cursor.getInt(cursor.getColumnIndex("age")));
		}
		cursor.close();
		//db.close();
		return person;
	}
	
	public List<Person> getPersons(Integer offset,Integer limit){
		List<Person> persons=new ArrayList<Person>();
		SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
		Cursor cursor;
		if(offset==null || limit==null)
			cursor = db.rawQuery("select * from person", new String[]{});
		else
			cursor = db.rawQuery("select * from person limit ?,?", new String[]{offset.toString(),limit.toString()});
		while(cursor.moveToNext()){
			Person person = new Person();
			person.setId(cursor.getLong(cursor.getColumnIndex("id")));
			person.setName(cursor.getString(cursor.getColumnIndex("name")));
			person.setAge(cursor.getInt(cursor.getColumnIndex("age")));
			persons.add(person);
		}
		cursor.close();
		//db.close();
		return persons;
	}
	
}
